USE [Test_Events]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetEventIndicatorCommentsByCompanyIds2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetEventIndicatorCommentsByCompanyIds2]
GO


CREATE  PROCEDURE [dbo].[spGetEventIndicatorCommentsByCompanyIds2]
	@CompanyIds NVARCHAR(MAX), @timestamp DATETIME
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)


	DECLARE @EventIndicatorComments TABLE
	(
		CompanyId uniqueidentifier NOT NULL,
		EventIndicatorId uniqueidentifier NOT NULL,
	    CommentId uniqueidentifier NOT NULL,
	    CommentType int NOT NULL,
	    Comment nvarchar(1200) NOT NULL,
	    CommentSortKey int NOT NULL,
		[Timestamp] [datetime] NOT NULL
	)

	INSERT INTO @EventIndicatorComments
	SELECT	EventIndicatorCompanyProfiles.CompanyId,
			EventIndicators.Id AS EventIndicatorId, 
			EventIndicators.ImpactCommentsId AS CommentId, 
			1 AS CommentType, 
			ImpactComments.Text AS Comment, 
            ImpactComments.SortKey AS CommentSortKey,
			@timestamp AS Timestamp
	FROM	dbo.EventIndicators AS EventIndicators 
	INNER JOIN dbo.EventIndicatorCompanyProfiles AS EventIndicatorCompanyProfiles ON EventIndicators.CompanyProfileId = EventIndicatorCompanyProfiles.Id 
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId
    INNER JOIN dbo.ImpactCommentsProperties AS ImpactCommentsProperties ON EventIndicators.ImpactCommentsId = ImpactCommentsProperties.Id
    INNER JOIN dbo.ImpactComments AS ImpactComments ON ImpactCommentsProperties.Id = ImpactComments.ImpactCommentsPropertyId
	WHERE   EventIndicatorCompanyProfiles.Status = 0


	INSERT INTO @EventIndicatorComments
	SELECT	EventIndicatorCompanyProfiles.CompanyId,
			EventIndicators.Id AS EventIndicatorId, 
			EventIndicators.RiskCommentsId AS CommentId, 
			2 AS CommentType, 
			RiskComments.Text AS Comment, 
            RiskComments.SortKey AS CommentSortKey,
			@timestamp AS Timestamp
	FROM	dbo.EventIndicators AS EventIndicators 
	INNER JOIN dbo.EventIndicatorCompanyProfiles AS EventIndicatorCompanyProfiles ON EventIndicators.CompanyProfileId = EventIndicatorCompanyProfiles.Id
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId
    INNER JOIN dbo.RiskCommentsProperties AS RiskCommentsProperties ON EventIndicators.RiskCommentsId = RiskCommentsProperties.Id
    INNER JOIN dbo.RiskComments AS RiskComments ON RiskCommentsProperties.Id = RiskComments.RiskCommentsPropertyId
	WHERE   EventIndicatorCompanyProfiles.Status = 0

	INSERT INTO @EventIndicatorComments
	SELECT	EventIndicatorCompanyProfiles.CompanyId,
			EventIndicators.Id AS EventIndicatorId, 
			EventIndicators.ManagementCommentsId AS CommentId, 
			3 AS CommentType, 
			ManagementComments.Text AS Comment, 
            ManagementComments.SortKey AS CommentSortKey,
			@timestamp AS Timestamp
	FROM	dbo.EventIndicators AS EventIndicators 
	INNER JOIN dbo.EventIndicatorCompanyProfiles AS EventIndicatorCompanyProfiles ON EventIndicators.CompanyProfileId = EventIndicatorCompanyProfiles.Id
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId
    INNER JOIN dbo.ManagementCommentsProperties AS ManagementCommentsProperties ON EventIndicators.ManagementCommentsId = ManagementCommentsProperties.Id
    INNER JOIN dbo.ManagementComments AS ManagementComments ON ManagementCommentsProperties.Id = ManagementComments.ManagementCommentsPropertyId
	WHERE   EventIndicatorCompanyProfiles.Status = 0

	INSERT INTO @EventIndicatorComments
	SELECT	EventIndicatorCompanyProfiles.CompanyId,
			EventIndicators.Id AS EventIndicatorId, 
			EventIndicators.OutlookCommentsId AS CommentId, 
			4 AS CommentType, 
			OutlookComments.Text AS Comment, 
            OutlookComments.SortKey AS CommentSortKey,
			@timestamp AS Timestamp
	FROM	dbo.EventIndicators AS EventIndicators
	INNER JOIN dbo.EventIndicatorCompanyProfiles AS EventIndicatorCompanyProfiles ON EventIndicators.CompanyProfileId = EventIndicatorCompanyProfiles.Id
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId
	INNER JOIN dbo.OutlookCommentsProperties AS OutlookCommentsProperties ON EventIndicators.OutlookCommentsId = OutlookCommentsProperties.Id
	INNER JOIN dbo.OutlookComments AS OutlookComments ON OutlookCommentsProperties.Id = OutlookComments.OutlookCommentsPropertyId
	WHERE   EventIndicatorCompanyProfiles.Status = 0

	SELECT CompanyId
	  ,EventIndicatorId
      ,CommentId
      ,CommentType
      ,Comment
      ,CommentSortKey
	  ,Timestamp
  FROM @EventIndicatorComments

END
GO


